﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using BE;
using BE.SearchCriteria;

namespace DAL
{
    public class PresentacionDAL : BaseDAL
    {
        public void InsertarPresentaciones(List<PresentacionBE> pListElemBE)
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                try
                {
                    cnn.Open();

                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Presentaciones_i";

                    SqlParameter oParamIdPresentacion = cmd.Parameters.Add(new SqlParameter("@IdPresentacion", SqlDbType.Int));
                    SqlParameter oParamIdArticulo = cmd.Parameters.Add(new SqlParameter("@IdArticulo", SqlDbType.Int));
                    SqlParameter oParamDescripcion = cmd.Parameters.Add(new SqlParameter("@Descripcion", SqlDbType.VarChar));                        
                    
                    foreach (var oPresentacion in pListElemBE)
                    {
                        oParamIdPresentacion.Value = oPresentacion.IdPresentacion;
                        oParamIdArticulo.Value = oPresentacion.IdArticulo;
                        oParamDescripcion.Value = oPresentacion.Descripcion;

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception)
                {
                    throw;
                }

            }
        }

        public void DeleteByIdArticulo(int pIdArticulo)
        {
            try
            {
                using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Presentaciones_d_ByIdarticulo";
                    cmd.Parameters.Add(new SqlParameter("@IdArticulo", pIdArticulo));
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception)
            { throw; }

        }

        public List<PresentacionBE> SeleccionarPorIdArticulo(int pIdArticulo)
        {
            List<PresentacionBE> listaBE = null;
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@IdArticulo", pIdArticulo));

                cmd.CommandText = "Presentaciones_GetByIdArticulo";
                cnn.Open();
                SqlDataReader reader = null;
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    listaBE = new List<PresentacionBE>();

                    while (reader.Read())
                    {
                        listaBE.Add(Construir(reader));
                    }
                }
                reader.Close();                

            }
            return listaBE;
        }

        private PresentacionBE Construir(SqlDataReader reader)
        {
            PresentacionBE oBE = new PresentacionBE();
            Object[] values = new Object[reader.FieldCount];

            reader.GetValues(values);

            oBE.IdPresentacion = Int32.Parse(reader["IdPresentacion"].ToString());
            oBE.IdArticulo = Int32.Parse(reader["IdArticulo"].ToString());
            oBE.Descripcion = reader["Descripcion"].ToString();
            if (reader["FechaAlta"] != DBNull.Value)
                oBE.FechaAlta = Convert.ToDateTime(reader["FechaAlta"]);

            return oBE;
        }

        public List<PresentacionBE> SeleccionarPorParametros(BaseSC pFiltroSC, int pPageSize, int? pPageNum, ref int pCountReg)
        {
            PresentacionSC wFiltroSC = (PresentacionSC)pFiltroSC;
            if (wFiltroSC == null)
                wFiltroSC = new PresentacionSC();

            List<PresentacionBE> listaDePresentacionBE = null;
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cnn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@IdPresentacion", wFiltroSC.IdPresentacion));
                cmd.Parameters.Add(new SqlParameter("@IdArticulo", wFiltroSC.IdArticulo));
                cmd.Parameters.Add(new SqlParameter("@Descripcion", wFiltroSC.IdArticulo));
                cmd.Parameters.Add(new SqlParameter("@FechaAlta", wFiltroSC.IdArticulo));
                cmd.Parameters.Add(new SqlParameter("@pagesize", pPageSize));
                cmd.Parameters.Add(new SqlParameter("@pagenum", pPageNum));

                SqlParameter p = new SqlParameter();
                p.ParameterName = "@PageCount";
                p.Direction = ParameterDirection.Output;
                p.SqlDbType = SqlDbType.Int;
                cmd.Parameters.Add(p);

                cmd.CommandText = "Presentaciones_GetAllPaginated";
                cnn.Open();
                SqlDataReader reader = null;
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    listaDePresentacionBE = new List<PresentacionBE>();

                    while (reader.Read())
                    {
                        PresentacionBE oPresentacionBE = Construir(reader);

                        listaDePresentacionBE.Add(oPresentacionBE);
                    }
                }
                reader.Close();
                pCountReg = (Int32)cmd.Parameters["@PageCount"].Value;

            }
            return listaDePresentacionBE;
        }


        public void Insertar(PresentacionBE pElemBE)
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Presentaciones_i";                    
                    cmd.Parameters.Add(new SqlParameter("@IdArticulo", pElemBE.IdArticulo));
                    cmd.Parameters.Add(new SqlParameter("@IdPresentacion", pElemBE.IdPresentacion));
                    cmd.Parameters.Add(new SqlParameter("@Descripcion", pElemBE.Descripcion));
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }

            }
        }

        public void Modificar(PresentacionBE pElemBE)
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Presentaciones_u";                    
                    cmd.Parameters.Add(new SqlParameter("@IdArticulo", pElemBE.IdArticulo));
                    cmd.Parameters.Add(new SqlParameter("@IdPresentacion", pElemBE.IdPresentacion));
                    cmd.Parameters.Add(new SqlParameter("@Descripcion", pElemBE.Descripcion));
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }

            }
        }


        public void Eliminar(int pIdArticulo, int pIdPresentacion)
        {
            using (SqlConnection cnn = new SqlConnection(GetConnectionString()))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = cnn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "Presentaciones_d";                    
                    cmd.Parameters.Add(new SqlParameter("@IdArticulo", pIdArticulo));
                    cmd.Parameters.Add(new SqlParameter("@IdPresentacion", pIdPresentacion));
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }

            }
        }

    }
}
